package com.tpc.control.jpa;

import java.util.Date;
import java.util.List;
import java.util.logging.Level;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import com.tpc.control.util.Formatting;

/**
 * A data access object (DAO) providing persistence and search support for
 * CommonData entities. Transaction control of the save(), update() and delete()
 * operations must be handled externally by senders of these methods or must be
 * manually added to each of these methods for data to be persisted to the JPA
 * datastore.
 * 
 * @see com.tpc.control.jpa.CommonData
 * @author MyEclipse Persistence Tools
 */

public class CommonDataDAO implements ICommonDataDAO {
	// property constants
	public static final String CUSTOMER = "customer";
	public static final String CUSTOMER_NAME = "customerName";
	public static final String CUSTOMER_PO_NO = "customerPoNo";
	public static final String DISTR_CHANNEL = "distrChannel";
	public static final String PIR_NO = "pirNo";
	public static final String DIVISION = "division";
	public static final String MATERIAL = "material";
	public static final String DESIGN_NO_FG = "designNoFg";
	public static final String DYELOT_LAB = "dyelotLab";
	public static final String QUANTITY = "quantity";
	public static final String UOM = "uom";
	public static final String FINISH_GOODS = "finishGoods";
	public static final String PO_NO = "poNo";
	public static final String DESIGN_NO_GREIGE = "designNoGreige";
	public static final String PLAN_GREIGE = "planGreige";
	public static final String GREIGE_REMARK = "greigeRemark";
	public static final String DYE_REMARK = "dyeRemark";
	public static final String DUE_SUBMIT_DATE = "dueSubmitDate";
	public static final String STATUS = "status";
	public static final String CURRENT_OPERATION = "currentOperation";
	public static final String USER_STATUS = "userStatus";
	public static final String TOT_PRODUCE_QTY = "totProduceQty";
	public static final String COLOR_STATUS = "colorStatus";
	public static final String CONFIRM_TYPE = "confirmType";
	public static final String USER_STATUS_CODE = "userStatusCode";
	public static final String OVERDUE_STATUS = "overdueStatus";
	public static final String RESERVE_DYE_REMARK = "reserveDyeRemark";

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	/**
	 * Perform an initial save of a previously unsaved CommonData entity. All
	 * subsequent persist actions of this entity should use the #update()
	 * method. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#persist(Object)
	 * EntityManager#persist} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * CommonDataDAO.save(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            CommonData entity to persist
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void save(CommonData entity) {
		EntityManagerHelper.log("saving CommonData instance", Level.INFO, null);
		try {
			getEntityManager().persist(entity);
			EntityManagerHelper.log("save successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("save failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Delete a persistent CommonData entity. This operation must be performed
	 * within the a database transaction context for the entity's data to be
	 * permanently deleted from the persistence store, i.e., database. This
	 * method uses the {@link javax.persistence.EntityManager#remove(Object)
	 * EntityManager#delete} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * CommonDataDAO.delete(entity);
	 * EntityManagerHelper.commit();
	 * entity = null;
	 * </pre>
	 * 
	 * @param entity
	 *            CommonData entity to delete
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void delete(CommonData entity) {
		EntityManagerHelper.log("deleting CommonData instance", Level.INFO,
				null);
		try {
			entity = getEntityManager().getReference(CommonData.class,
					entity.getId());
			getEntityManager().remove(entity);
			EntityManagerHelper.log("delete successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("delete failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Persist a previously saved CommonData entity and return it or a copy of
	 * it to the sender. A copy of the CommonData entity parameter is returned
	 * when the JPA persistence mechanism has not previously been tracking the
	 * updated entity. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#merge(Object) EntityManager#merge}
	 * operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * entity = CommonDataDAO.update(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            CommonData entity to update
	 * @return CommonData the persisted CommonData entity instance, may not be
	 *         the same
	 * @throws RuntimeException
	 *             if the operation fails
	 */
	public CommonData update(CommonData entity) {
		EntityManagerHelper.log("updating CommonData instance", Level.INFO,
				null);
		try {
			CommonData result = getEntityManager().merge(entity);
			EntityManagerHelper.log("update successful", Level.INFO, null);
			return result;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("update failed", Level.SEVERE, re);
			throw re;
		}
	}

	public CommonData findById(CommonDataId id) {
		EntityManagerHelper.log("finding CommonData instance with id: " + id,
				Level.INFO, null);
		try {
			CommonData instance = getEntityManager().find(CommonData.class, id);
			return instance;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Find all CommonData entities with a specific property value.
	 * 
	 * @param propertyName
	 *            the name of the CommonData property to query
	 * @param value
	 *            the property value to match
	 * @return List<CommonData> found by query
	 */
	@SuppressWarnings("unchecked")
	public List<CommonData> findByProperty(String propertyName,
			final Object value) {
		EntityManagerHelper.log("finding CommonData instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			final String queryString = "select model from CommonData model where model."
					+ propertyName + "= :propertyValue";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", value);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<CommonData> findByCustomer(Object customer) {
		return findByProperty(CUSTOMER, customer);
	}

	public List<CommonData> findByCustomerName(Object customerName) {
		return findByProperty(CUSTOMER_NAME, customerName);
	}

	public List<CommonData> findByCustomerPoNo(Object customerPoNo) {
		return findByProperty(CUSTOMER_PO_NO, customerPoNo);
	}

	public List<CommonData> findByDistrChannel(Object distrChannel) {
		return findByProperty(DISTR_CHANNEL, distrChannel);
	}

	public List<CommonData> findByPirNo(Object pirNo) {
		return findByProperty(PIR_NO, pirNo);
	}

	public List<CommonData> findByDivision(Object division) {
		return findByProperty(DIVISION, division);
	}

	public List<CommonData> findByMaterial(Object material) {
		return findByProperty(MATERIAL, material);
	}

	public List<CommonData> findByDesignNoFg(Object designNoFg) {
		return findByProperty(DESIGN_NO_FG, designNoFg);
	}

	public List<CommonData> findByDyelotLab(Object dyelotLab) {
		return findByProperty(DYELOT_LAB, dyelotLab);
	}

	public List<CommonData> findByQuantity(Object quantity) {
		return findByProperty(QUANTITY, quantity);
	}

	public List<CommonData> findByUom(Object uom) {
		return findByProperty(UOM, uom);
	}

	public List<CommonData> findByFinishGoods(Object finishGoods) {
		return findByProperty(FINISH_GOODS, finishGoods);
	}

	public List<CommonData> findByPoNo(Object poNo) {
		return findByProperty(PO_NO, poNo);
	}

	public List<CommonData> findByDesignNoGreige(Object designNoGreige) {
		return findByProperty(DESIGN_NO_GREIGE, designNoGreige);
	}

	public List<CommonData> findByPlanGreige(Object planGreige) {
		return findByProperty(PLAN_GREIGE, planGreige);
	}

	public List<CommonData> findByGreigeRemark(Object greigeRemark) {
		return findByProperty(GREIGE_REMARK, greigeRemark);
	}

	public List<CommonData> findByDyeRemark(Object dyeRemark) {
		return findByProperty(DYE_REMARK, dyeRemark);
	}

	public List<CommonData> findByDueSubmitDate(Object dueSubmitDate) {
		return findByProperty(DUE_SUBMIT_DATE, dueSubmitDate);
	}

	public List<CommonData> findByStatus(Object status) {
		return findByProperty(STATUS, status);
	}

	public List<CommonData> findByCurrentOperation(Object currentOperation) {
		return findByProperty(CURRENT_OPERATION, currentOperation);
	}

	public List<CommonData> findByUserStatus(Object userStatus) {
		return findByProperty(USER_STATUS, userStatus);
	}

	public List<CommonData> findByTotProduceQty(Object totProduceQty) {
		return findByProperty(TOT_PRODUCE_QTY, totProduceQty);
	}

	public List<CommonData> findByColorStatus(Object colorStatus) {
		return findByProperty(COLOR_STATUS, colorStatus);
	}

	public List<CommonData> findByConfirmType(Object confirmType) {
		return findByProperty(CONFIRM_TYPE, confirmType);
	}

	public List<CommonData> findByUserStatusCode(Object userStatusCode) {
		return findByProperty(USER_STATUS_CODE, userStatusCode);
	}

	public List<CommonData> findByOverdueStatus(Object overdueStatus) {
		return findByProperty(OVERDUE_STATUS, overdueStatus);
	}

	public List<CommonData> findByReserveDyeRemark(Object reserveDyeRemark) {
		return findByProperty(RESERVE_DYE_REMARK, reserveDyeRemark);
	}

	/**
	 * Find all CommonData entities.
	 * 
	 * @return List<CommonData> all CommonData entities
	 */
	@SuppressWarnings("unchecked")
	public List<CommonData> findAll() {
		EntityManagerHelper.log("finding all CommonData instances", Level.INFO,
				null);
		try {
			final String queryString = "select model from CommonData model";
			Query query = getEntityManager().createQuery(queryString);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find all failed", Level.SEVERE, re);
			throw re;
		}
	}
	
	/* Customize */	
	public List<CommonData> findByReport(String option, boolean selectSale, boolean selectPir, 
			Object plantId1, Object plantId2, 
			Object division1, Object division2, 
			Object prodNo1, Object prodNo2,
			Object material1, Object material2,
			Object createDate1, Object createDate2,
			Object planStartDate1, Object planStartDate2,
			Object customer1, Object customer2,
			Object saleOrder1, Object saleOrder2,
			Object saleDueDate1, Object saleDueDate2,
			Object customerPo1, Object customerPo2,
			Object pirNo1, Object pirNo2,
			Object pirDueDate1, Object pirDueDate2) {
		EntityManagerHelper.log("findByQuery1 CommonData instance", Level.INFO, null);
		try {
			System.out.println("plantId: "+plantId1+"::"+plantId2);
			System.out.println("division: "+division1+"::"+division2);
			System.out.println("production: "+prodNo1+"::"+prodNo2);
			System.out.println("material: "+material1+"::"+material2);
			System.out.println("createDate: "+createDate1+"::"+createDate2);
			System.out.println("planStartDate: "+planStartDate1+"::"+planStartDate2);
			
			System.out.println("customer: "+customer1+"::"+customer2);
			System.out.println("saleOrder: "+saleOrder1+"::"+saleOrder2);
			System.out.println("saleDueDate: "+saleDueDate1+"::"+saleDueDate2);
			System.out.println("customerPo: "+customerPo1+"::"+customerPo2);
			
			System.out.println("pirNo: "+pirNo1+"::"+pirNo2);
			System.out.println("pirDueDate: "+pirDueDate1+"::"+pirDueDate2);
			
			String[] plantId = Formatting.generateFromToText(plantId1, plantId2, 4);
			String[] division = Formatting.generateFromToText(division1, division2, 2);
			String[] production = Formatting.generateFromToText(prodNo1, prodNo2, 12);
			String[] material = Formatting.generateFromToText(material1, material2, 18);
			Date[] createDate = Formatting.generateFromToDate(createDate1, createDate2);
			Date[] planStartDate = Formatting.generateFromToDate(planStartDate1, planStartDate2);
			String[] customer = Formatting.generateFromToText(customer1, customer2, 10);
			String[] saleOrder = Formatting.generateFromToText(saleOrder1, saleOrder2, 10);
			Date[] saleDueDate = Formatting.generateFromToDate(saleDueDate1, saleDueDate2);
			String[] customerPo = Formatting.generateFromToText(customerPo1, customerPo2, 35);
			String[] pirNo = Formatting.generateFromToText(pirNo1, pirNo2, 10);
			Date[] pirDueDate = Formatting.generateFromToDate(pirDueDate1, pirDueDate2);

			String queryString = "select model from CommonData model where (model.id.productionNo <> '') ";
			if (option.equalsIgnoreCase("w") || option.equalsIgnoreCase("o")) {
//				queryString += " and model.userStatus <> 'จบการผลิต' ";
				queryString += " and model.colorStatus <> 'green' ";
				if (option.equalsIgnoreCase("w")) {
					queryString += " and model.colorStatus <> 'white' ";
				}
			}
			if (!((String)plantId1).equals("") || !((String)plantId2).equals("")) {
				queryString += " and (model.plant.plantId " + " between :propertyValue1 "
					+ " and :propertyValue2) ";
			}
			if (!((String)division1).equals("") || !((String)division2).equals("")) {
				queryString += " and  (model.division " + " between :propertyValue3 "
					+ " and :propertyValue4) ";
			}
			if (!((String)prodNo1).equals("") || !((String)prodNo2).equals("")) {
				queryString += " and  (model.id.productionNo " + " between :propertyValue5 "
					+ " and :propertyValue6) ";
			}
			if (!((String)material1).equals("") || !((String)material2).equals("")) {
				queryString += " and  (model.material " + " between :propertyValue7 "
					+ " and :propertyValue8) ";
			}
			if (createDate1!=null || createDate2!=null) {
				queryString += " and  (model.prodCreateDate " + " between :propertyValue9 "
					+ " and :propertyValue10) ";
			}
			if (planStartDate1!=null || planStartDate2!=null) {
				queryString += " and  (model.planStartDate " + " between :propertyValue11 "
					+ " and :propertyValue12) ";
			}
			
			if (selectSale) {
				queryString += " and  (model.customer <> '') ";
				if (!((String)customer1).equals("") || !((String)customer2).equals("")) {
					queryString += " and  (model.customer " + " between :propertyValue13 "
					+ " and :propertyValue14) ";
				}
				if (!((String)saleOrder1).equals("") || !((String)saleOrder2).equals("")) {
					queryString += " and  (model.saleData.id.saleOrder " + " between :propertyValue15 "
					+ " and :propertyValue16) ";
				}
				if (saleDueDate1!=null || saleDueDate2!=null) {
					queryString += " and  (model.dueDate " + " between :propertyValue17 "
					+ " and :propertyValue18) ";
				}
				if (!((String)customerPo1).equals("") || !((String)customerPo2).equals("")) {
					queryString += " and  (model.customerPoNo " + " between :propertyValue19 "
					+ " and :propertyValue20) ";
				}
			}
			
			if (selectPir) {
				queryString += " and  (model.customer = '') ";
				if (!((String)pirNo1).equals("") || !((String)pirNo2).equals("")) {
					queryString += " and  (model.pirNo " + " between :propertyValue13 "
					+ " and :propertyValue14) ";
				}
				if (pirDueDate1!=null || pirDueDate2!=null) {
					queryString += " and  (model.pirDueDate " + " between :propertyValue15 "
					+ " and :propertyValue16) ";
				}
			}
			
			queryString += " order by model.plant.plantId ";
			System.out.println("queryString: "+queryString);
			
			Query query = getEntityManager().createQuery(queryString);
			if (!((String)plantId1).equals("") || !((String)plantId2).equals("")) {
				query.setParameter("propertyValue1", plantId[0]);
				query.setParameter("propertyValue2", plantId[1]);
			}
			if (!((String)division1).equals("") || !((String)division2).equals("")) {
				query.setParameter("propertyValue3", division[0]);
				query.setParameter("propertyValue4", division[1]);
			}
			if (!((String)prodNo1).equals("") || !((String)prodNo2).equals("")) {
				query.setParameter("propertyValue5", production[0]);
				query.setParameter("propertyValue6", production[1]);
			}
			if (!((String)material1).equals("") || !((String)material2).equals("")) {
				query.setParameter("propertyValue7", material[0]);
				query.setParameter("propertyValue8", material[1]);
			}
			if (createDate1!=null || createDate2!=null) {
				query.setParameter("propertyValue9", createDate[0]);
				query.setParameter("propertyValue10", createDate[1]);
			}
			if (planStartDate1!=null || planStartDate2!=null) {
				query.setParameter("propertyValue11", planStartDate[0]);
				query.setParameter("propertyValue12", planStartDate[1]);
			}
			
			if (selectSale) {
				if (!((String)customer1).equals("") || !((String)customer2).equals("")) {
					customer[0] = Formatting.generateCustomerNo(customer[0]);
					customer[1] = Formatting.generateCustomerNo(customer[1]);
					query.setParameter("propertyValue13", customer[0]);
					query.setParameter("propertyValue14", customer[1]);
				}
				if (!((String)saleOrder1).equals("") || !((String)saleOrder2).equals("")) {
					query.setParameter("propertyValue15", saleOrder[0]);
					query.setParameter("propertyValue16", saleOrder[1]);
				}
				if (saleDueDate1!=null || saleDueDate2!=null) {
					query.setParameter("propertyValue17", saleDueDate[0]);
					query.setParameter("propertyValue18", saleDueDate[1]);
				}
				if (!((String)customerPo1).equals("") || !((String)customerPo2).equals("")) {
					query.setParameter("propertyValue19", customerPo[0]);
					query.setParameter("propertyValue20", customerPo[1]);
				}
			}
			if (selectPir) {
				if (!((String)pirNo1).equals("") || !((String)pirNo2).equals("")) {
					query.setParameter("propertyValue13", pirNo[0]);
					query.setParameter("propertyValue14", pirNo[1]);
				}
				if (pirDueDate1!=null || pirDueDate2!=null) {
					query.setParameter("propertyValue15", pirDueDate[0]);
					query.setParameter("propertyValue16", pirDueDate[1]);
				}
			}
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	
	public List<CommonData> findBySale(Object saleOrder, Object saleItem) {
		EntityManagerHelper.log("findBySaleOrder CommonData instance", Level.INFO, null);
		try {
			System.out.println("saleOrder: "+saleOrder);
			System.out.println("saleItem: "+saleItem);	

			String queryString = "select model from CommonData model where " +
					"model.saleOrder = :propertyValue1 and " +
					"model.saleItem = :propertyValue2 ";
			
			System.out.println("queryString: "+queryString);
			
			Query query = getEntityManager().createQuery(queryString);
			
			query.setParameter("propertyValue1", saleOrder);
			query.setParameter("propertyValue2", saleItem);
			
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
	public List<CommonData> findBySaleOrderAndSaleItem(Object propertyName1,
			Object propertyName2) {
		EntityManagerHelper.log("finding CommonData instance with Sale_Order and Sale_Item "
				, Level.INFO, null);
		try {
			final String queryString = "select model from CommonData model where model.saleData.id.saleOrder" +
					" = :propertyName1 and model.saleData.id.saleItem = :propertyName2";
				
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyName1", propertyName1);
			query.setParameter("propertyName2", propertyName2);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}
}